package com.naiterui.ehp.bs.agent.repository;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.naiterui.common.repository.core.BaseJpaRepository;
import com.naiterui.ehp.bs.agent.entity.AgCustomSummary;

import java.util.List;

/**
 * 客户数据汇总
 * @author yiren
 * @since 2022/2/15
 */
@Repository
public interface IAgCustomSummaryRepository extends BaseJpaRepository<AgCustomSummary, Long> {
	@Modifying
	@Query(value = "insert into ag_custom_summary" +
        "(agent_id,doctor_id, area_id, provice_area_id, generate_date, recipel_total,consult_total, video_total, order_total, order_price_total, consult_price_total, video_price_total, repeat_number, paid_number, unpaid_number) "
        +
			"select agent_id,doctor_id, area_id, provice_area_id, now(), sum(recipel_total) recipel_total, sum(consult_total) consult_total, sum(video_total) video_total, sum(order_total) order_total,sum(order_price_total) order_price_total, sum(consult_price_total) consult_price_total, sum(video_price_total) video_price_total, sum(repeat_number) repeat_number, sum(paid_number) paid_number,sum(unpaid_number) unpaid_number " +
			"from as_recipel_count group by agent_id,doctor_id, area_id, provice_area_id  ", nativeQuery = true)
	void insertCustomSummary();

	@Modifying
	@Query(value = "delete from ag_custom_summary", nativeQuery = true)
	void deleteAllData();

    AgCustomSummary findFirstByDoctorId(Long doctorId);

	@Query(value = "select sum(recipel_total) recipel_total, sum(order_price_total) order_price_total from ag_custom_summary where agent_id = :agentId group by agent_id", nativeQuery = true)
	List<Object[]> getTotalByAgentId(@Param("agentId") Long agentId);
}
